SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

drop view v_ReservationTime
go
CREATE VIEW [dbo].[v_ReservationTime]
AS

--first part display stamping data
SELECT     s.UNITID, s.Dept AS DEPTID, s.SMANID, s.NAME AS SNAME, gs.GSALID, gs.WRKORDNO AS WORK_ORDER_ID, gs.VEHIID, gs.CUSTNO, gs.WTRID, gs.CONFNAME, 
                      gs.CONLNAME, gs.UWTEL AS CONTACT_TEL, gs.UEMAIL AS CONTACT_EMAIL, st.STARTDT AS STARTTIME, isnull(st.ENDDT,GETDATE()+0.02) AS ENDTIME, 
                      cast(datediff(minute,st.STARTDT,isnull(st.ENDDT,GETDATE()+0.02) ) as decimal)/60 as DURATION, 
                      v.LICNO AS LIC_NO, v.SERIALNO, v.MAKE, v.MODEL, v.SUBMODEL, c.LNAME, c.FNAME, c.email, c.WTEL, gs.STYPE AS TRANSACTION_TYPE, 
                      c2.C2 AS TRANSACTION_NOTE, gs.ESTDELD AS PICKUP_DATE, gs.ESTDELTIME AS PICKUP_TIME, 
                      gr.NOTE,
                      gs.RECEIVER, 
                      gr.GROWID, 
                      gs.SERVD, gs.WENDD, 
                      st.ENDDT AS WENDDT, st.STAMID,st.STATUS as STAMP_STATUS, gs.WOREADYDATE as WOREADYDATE, gs.CUSTWAIT as CUSTWAIT, gs.STATUS as WOSTATUS
                     
FROM         
                      dbo.VEHI AS v INNER JOIN
                      dbo.ALL_GSAL AS gs ON v.VEHIID = gs.VEHIID INNER JOIN
                      dbo.CUST AS c ON gs.CUSTNO = c.CUSTNO 
                   --   dbo.ALL_GTRES AS gt INNER JOIN
                     -- dbo.ALL_GTRS AS g ON gt.WTRID = g.WTRID ON gs.WTRID = g.WTRID AND 
                      --gs._UNITID = gt._UNITID AND gs._UNITID = g._UNITID ON s.SMANID = gt.RESID AND 
                      --s.UNITID = gs._UNITID 
                      INNER JOIN dbo.ALL_GROW AS gr ON gs.GSALID = gr.GSALID AND gs._UNITID = gr._UNITID 
                      INNER JOIN
                      dbo.ALL_CORW AS c2 ON gs.STYPE = c2.C1 AND gs._UNITID = c2._UNITID 
                     INNER JOIN dbo.ALL_GRST AS grst ON grst._UNITID=gs._UNITID and gr.GSALID = grst.GSALID AND gr.GROWID = grst.GROWID 
                     INNER JOIN dbo.ALL_STAM AS st ON st._UNITID = gs._UNITID and st.STAMID = grst.STAMID 
                     INNER JOIN SMAN s on st.SMANID=s.SMANID
WHERE     (c2.CODAID = 'LIIKETAPTY') AND (c2.C7 LIKE '%K%') and c2.C8 not like '%O%' and gs.STATUS !='M'
--second part is booking data
union all
SELECT     isnull(s.UNITID,s2._UNITID), s.Dept AS DEPTID, isnull(s.SMANID,s2.RESID), isnull(s.NAME,s2.name) AS SNAME, gs.GSALID, gs.WRKORDNO AS WORK_ORDER_ID, gs.VEHIID, gs.CUSTNO, gs.WTRID, gs.CONFNAME, 
                      gs.CONLNAME, gs.UWTEL AS CONTACT_TEL, gs.UEMAIL AS CONTACT_EMAIL, gt.STARTDT AS STARTTIME, gt.ENDDT AS ENDTIME, gt.DURATION, 
                      v.LICNO AS LIC_NO, v.SERIALNO, v.MAKE, v.MODEL, v.SUBMODEL, c.LNAME, c.FNAME, c.email, c.WTEL, gs.STYPE AS TRANSACTION_TYPE, 
                      c2.C2 AS TRANSACTION_NOTE, gs.ESTDELD AS PICKUP_DATE, gs.ESTDELTIME AS PICKUP_TIME, 
                --      gr.NOTE,
                      'x' as NOTE, 
                      gs.RECEIVER, 
                  --    gr.GROWID, 
                      1 as GROWID,
                      gs.SERVD, gs.WENDD, 
                   --   st.ENDDT AS WENDDT, st.STAMID
                      null AS WENDDT, -1 as STAMID, null as STAMP_STATUS,  gs.WOREADYDATE as WOREADYDATE, gs.CUSTWAIT as CUSTWAIT, gs.STATUS as WOSTATUS
FROM         
                      dbo.VEHI AS v INNER JOIN
                      dbo.ALL_GSAL AS gs ON v.VEHIID = gs.VEHIID INNER JOIN
                      dbo.CUST AS c ON gs.CUSTNO = c.CUSTNO INNER JOIN
                      dbo.ALL_GTRES AS gt 
						left join dbo.SMAN s on s.SMANID = gt.RESID AND 
                      s.UNITID = gt._UNITID and gt.RESTYPE!=1
                      left join dbo.ALL_GRES s2 on s2.RESID = gt.RESID AND 
                      s2._UNITID = gt._UNITID and gt.RESTYPE=1
                      INNER JOIN
                      dbo.ALL_GTRS AS g ON gt.WTRID = g.WTRID ON gs.WTRID = g.WTRID AND 
                      gs._UNITID = gt._UNITID AND gs._UNITID = g._UNITID  
                   --   INNER JOIN dbo.ALL_GROW AS gr ON gs.GSALID = gr.GSALID AND gs._UNITID = gr._UNITID 
                      INNER JOIN
                      dbo.ALL_CORW AS c2 ON gs.STYPE = c2.C1 AND gs._UNITID = c2._UNITID 
                   --   LEFT OUTER JOIN dbo.ALL_GRST AS grst ON grst._UNITID=gs._UNITID and gr.GSALID = grst.GSALID AND gr.GROWID = grst.GROWID 
                   --   LEFT OUTER JOIN dbo.ALL_STAM AS st ON st._UNITID = gs._UNITID and st.STAMID = grst.STAMID and st.SMANID=gr.SMANID
WHERE     (c2.CODAID = 'LIIKETAPTY') AND (c2.C7 LIKE '%K%') and c2.C8 not like '%O%' and gt.ENDDT > gt.STARTDT
and gs.STATUS !='M'


GO



grant select on v_ReservationTime to public

go